/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.esisar.picaso.requetes;

import fr.esisar.picaso.entites.Album;
import fr.esisar.picaso.entites.Commande;
import fr.esisar.picaso.entites.Format;
import fr.esisar.picaso.moteur.Connexion;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author Jean-Baptiste
 */
public class RequetesCommande {
	
	
    public void ajouterCommande(Commande c, int idClient) throws SQLException{
    	Connection connexion = Connexion.getConnection();
    	Statement st = connexion.createStatement();
    	String requete = "SELECT MAX(idCommande) FROM U0.Commande";
    	ResultSet rs = st.executeQuery(requete);
    	rs.next();
    	int idCommande = rs.getInt("MAX(idCommande)")+1;
    	for(int i=0 ; i<c.getAlbumsCommandes().size() ; i++){
    		Album a = c.getAlbumsCommandes().get(i);
    		Format f = c.getFormatsCommandes().get(i);
    		setCommande(idCommande, a.getId(), f.getId(), c.getQtes().get(i), new Date() , idClient);
    	}
    }
	
	
    public void setCommande(int idCommande, int idAlbum, int idFormat,int quantite, Date date, int idClient) throws SQLException{
    
        Connection connexion = Connexion.getConnection();
        Statement st = connexion.createStatement();
        final DateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd");
        String requete = "SELECT prix FROM U0.Format WHERE idFormat="+idFormat;
        ResultSet rs = st.executeQuery(requete);
        rs.next();
        int prix = quantite * rs.getInt("prix");
        
        requete = "INSERT INTO u0.Commande(idCommande, prixTotal, dateCommande, idClient)"
                + " VALUES("+idCommande+","+prix+",to_date('"+dateFormat.format(date)+"','YYYY-MM-DD'),"+idClient+")";
        st.executeQuery(requete);
        
        requete = "INSERT INTO u0.Compose(quantite, idCommande, idAlbum, idFormat)"
                + " VALUES("+quantite+","+idCommande+","+idAlbum+","+idFormat+")";
        st.executeQuery(requete);
        
        connexion.commit();
    }
    
    public ArrayList<String> getListeCommande(int idCLient) throws SQLException{
            
        ArrayList<String> listeCommande = new ArrayList<String>();

        Connection connexion = Connexion.getConnection();
        Statement st = connexion.createStatement();

        String requete = "SELECT DISTINCT * FROM U0.Commande";
        ResultSet rs = st.executeQuery(requete);

        while(rs.next()) {
                listeCommande.add(rs.getString("prixTotal")+" "+rs.getDate("date"));
        }
        return listeCommande;
    }
}
    